Solution: Model the Subtypes Using Inheritance

Whenever EAV seems like the right design, we should take a second look before we implement it. In most cases, we will probably find that our project’s data can be modeled in a traditional table design more easily and with greater assurance of data integrity.

There are several ways to store such data without using EAV. Most solutions work best when we have a finite number of subtypes, and we know the attribute of each subtype. Our choice of the best solution depends on how we intend to query the data, so we should decide the design on a case-by-case basis. Let’s discuss these first. There are three types of inheritance we are going to discuss, namely:

• Single Table Inheritance
• Concrete Table Inheritance
• Class Table Inheritance

Single Table Inheritance#

The simplest design is to store all related types in one table, with distinct columns for every attribute that exists in any type. Under this design, we use one attribute to define the subtype of a given row. In our example, this attribute is called issue_type. Some attributes are common to all subtypes. Many attributes are subtype-specific, and these columns must be given a null value in every such row that stores an object for which the attribute does not apply; this would result in the columns with non-null values becoming sparse.

The name for this design comes from Martin Fowler’s book Patterns of Enterprise Application Architecture.

As new object types are introduced, the database must accommodate the attributes that describe these new object types. We must alter the table to add more columns as we add distinct attributes for the new object types. We may encounter a practical limit on the number of columns per table.

Another limitation of Single Table Inheritance is that there is no metadata to define which attributes belong to which subtypes. In our application, we can ignore some attributes if we know that they don’t apply to the object subtype on a given row. But we must track manually which attributes are applicable to each subtype. It would be better if we could use metadata to define this in the database.

Single Table Inheritance is best when we have a few subtypes and a few subtype-specific attributes, and we need to use a single-table database access pattern like Active Record.

Concrete Table Inheritance#

Another solution is to create a separate table for each subtype. Every table contains the same attributes that are common to the base type, as well as the respective subtype-specific attribute. The name of this design also comes from Martin Fowler’s book.

An advantage of Concrete Table Inheritance over Single Table Inheritance is that we are prevented from storing a row containing values for attributes that don’t apply to that row’s subtype. If we reference an attribute column that doesn’t exist in that table, the database informs us of the error automatically. For example, the severity column does not appear in the FeatureRequests table:

Let’s see how it works in the playground below.

Another advantage of Concrete Table Inheritance is that we don’t need an extra attribute to define the subtype in each row, as we do in the Single Table Inheritance design.

However, it’s hard to tell the common attributes from subtype-specific attributes. Also, if we add a new attribute to the set of common attributes, we must alter every subtype table.

No metadata shows that the data stored in these subtype tables belong to related objects. If a programmer who is new to our project looks at the table definitions, they would see that some columns are common to all these subtype tables. Still, the metadata would not itself tell whether any logical relationship exists or whether the tables have similarities merely by coincidence.

Searching all objects regardless of their subtypes is also complicated if each subtype is stored in a separate table. To make this query easier, we can define a view that comprises the union of the tables, i.e., the common attributes.

Try it yourself in the following playground.

The Concrete Table Inheritance design is best used when we seldom need to query against all subtypes at once.

Class Table Inheritance#

A third solution mimics inheritance, as though tables were object-oriented classes. Under this design, we create a single table for the base type, containing attributes common to all subtypes. Then for each subtype, we create another table with a primary key that also serves as a foreign key to the base table. The name of this design also comes from Martin Fowler’s book.

The one-to-one relationship is enforced by the metadata since the dependent table’s foreign key is also a primary key and thus must be unique. This solution provides an efficient way to search against all subtypes, as long as our search references only the base type’s attributes. Once we’ve found the entries that match our search, we can get the subtype-specific attributes by querying against the respective subtype tables.

We don’t need to know from the row in the base table what subtype the row represents. As long as we have a small number of subtypes, we can write a join against all of them at once, producing a sparse result set, like in the Single Table Inheritance table. For a given row in which the attribute doesn’t apply, the attributes are null.

Class Table Inheritance is also a good candidate for defining a VIEW.

This design is best when we often need to query across all subtypes, referencing the columns they have in common.